<?php
header("Content-type: text/html; charset=utf-8");
error_reporting(E_ALL || ~E_NOTICE);
	$sqlserver  = '192.168.1.10'; 
    $sqldatabase= 'GT';
    $sqluser    = 'sa2';
    $sqlpassword= 'sasa';
    $sqlconnection = odbc_connect("Driver={SQL Server};Server=$sqlserver;Database=$sqldatabase;", $sqluser, $sqlpassword);
    
   if($_POST['sortField']){
	   if($_POST['sortField']=='U_DocDate'){
		    $orderby    = 'order BY t.'. $_POST['sortField'] .' '. $_POST['sortOrder'].' ,U_OrderId ';
	   }else
		    $orderby    = 'order BY '. $_POST['sortField'] .' '. $_POST['sortOrder'];
   
	}else{
		$orderby = 'order by  U_DocDate desc ,U_OrderId';
	}
	$pagesize   = $_POST['pageSize']?:20;
	$page       = $_POST['pageIndex'];
	///
	$temp    = $pagesize*$page;
	$total   = 0;
	$wheres  = $_POST['q'];
	$countsql= 'select count(1) as total from [@ti_z0081] as t LEFT JOIN [@ti_z0080] as z on t.DocEntry=z.DocEntry where 1=1 ';
	$sqlquery = '';
	foreach($wheres as $key=>$where){
		if($where){
		    //检查条件是否为中文字符
		    if(cnCheck($where)){
		        $where = iconv("utf-8","gb2312", $where);
		        $sqlquery.= " and {$key} like '%{$where}%'  ";
		        $countsql.= " and {$key} like '%{$where}%'  ";
		    }else{
		        $where = iconv("utf-8","gb2312", $where);
		        $sqlquery.= " and {$key} = '{$where}'  ";
		        $countsql.= " and {$key} = '{$where}'  ";
		    }
			unset($where);
		}	
	}

	
	if($_POST['udocdate']){
		$start = $_POST['udocdate']['start'];
		$end  =  $_POST['udocdate']['end'];
		$sqlquery.= "and z.U_DocDate between '{$start}' and '{$end}' ";  
	    $countsql.= "and z.U_DocDate between '{$start}' and '{$end}' ";  
	}
	
	if($_POST['statuslist']){
		$statusStr= $_POST['statuslist'];
		$sqlquery.= " AND U_OStatus in ({$statusStr}) ";
		$countsql.= " AND U_OStatus in ({$statusStr}) ";
	}


	$sqlquery="SELECT * FROM ( SELECT row_number () OVER (  {$orderby} ) id ,* FROM ( SELECT ot.QryGroup45,U_CardCode, t.U_Total AS total, t.* FROM [@ti_z0081] AS t LEFT JOIN [@ti_z0080] AS z ON t.DocEntry = z.DocEntry    LEFT JOIN OITM ot on ot.ItemCode =  t.u_itemcode   where 1=1 {$sqlquery} ) t1 ) t2 where id>{$temp} and id<=".($temp+$pagesize);
	//查询总数量
	$total_res=odbc_exec($sqlconnection,$countsql);
	while(odbc_fetch_row($total_res)) {
	$total = odbc_result($total_res, 'total');
	}
	//数据查询
	$result=odbc_exec($sqlconnection,$sqlquery);  
    while ($data[] = odbc_fetch_array($result));
	odbc_close($sqlconnection);
	//error_log(var_export($data,1));
	$keylist = array_keys($data[0]);
	//将结果转码utf-8
	foreach($data as $key=>$value){
	
		foreach($keylist as $keyname){
			
			$data[$key][$keyname]=iconv("gbk","utf-8//IGNORE",$value[$keyname]);
			
		}
	
		 $data[$key]['total']= sprintf("%.2f",  $data[$key]['total']);
		 $data[$key]['U_NoDPrc']= sprintf("%.2f",  $data[$key]['U_NoDPrc']);
		
		if($data[$key]['U_OTPhone']){
		$data[$key]['U_OTPhone']='<a href="sip:'.$data[$key]['U_OTPhone'].'" title="'.$data[$key]['U_Cstname'].'"  >'.$data[$key]['U_OTPhone'].'</a>';
		}
		//解决一条数据包含多个电话号码
		$temp_tell = explode(' ',$data[$key]['U_Phone']);
		$data[$key]['U_Phone']='';
		foreach($temp_tell as $t){
			if(count($temp_tell)>1){
				$data[$key]['U_Phone'].='<a href="sip:'.$t.'" title="'.$data[$key]['U_Cstname'].'"  >'.$t.'</a>'.'</br>';
			}else
			$data[$key]['U_Phone'].='<a href="sip:'.$t.'" title="'.$data[$key]['U_Cstname'].'"  >'.$t.'</a>';
		}				
	}
	   
    	 $count =count($data)-1;
    	unset($data[$count]); 
        $res=array('total'=>$total,'data'=>$data);
        $res =json_encode($res,JSON_UNESCAPED_UNICODE);
        echo $res;
    	exit;
	    /**
    	 * 检查是否为中文
    	 * @param unknown $str
    	 * @return boolean
    	 */
    	function cnCheck($str){
    	    if (preg_match("/[\x7f-\xff]/", $str)) {
    	       return  true;
    	    }else{
    	       return false;
    	    }
    	}
	
	
